articles

Home / DeveloperSection / Articles / How Can Export Data from WebGrid to Excel in MVC

How Can Export Data from WebGrid to Excel in MVC

How Can Export Data from WebGrid to Excel in MVC

Anonymous User 3069 17-Sep-2018

First Create a Database and Table

The database name is "APCRUD "

Table Name is "Contac"

How Can Export Data from WebGrid to Excel in MVC

How Can Export Data from WebGrid to Excel in MVC

The Second Step is to create a new project in the visual studio in MVC(empty) 

How Can Export Data from WebGrid to Excel in MVC

Third Step is to create a repository pattern with Entity Framework

How Can Export Data from WebGrid to Excel in MVC

1). IBookRepository.cs

using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MvcRepo.Models.DAL
{
    interface IBookRepository
    {
        IEnumerable<BookTbl> GetBooks();
        BookTbl GetBookByID(int bookId);
        void InsertBook(BookTbl book);
        void DeleteBook(int bookId);
        void UpdateBook(BookTbl book);
        void Save();
    }
}

2). BookRepository.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace MvcRepo.Models.DAL
{
    public class BookRepository : IBookRepository
    {
        private DataContext _context;
        public BookRepository(DataContext context)
        {
            _context = context;
        }
        public IEnumerable<BookTbl> GetBooks()
        {
            return _context.BookTbls.ToList();
        }
        public BookTbl GetBookByID(int bookId)
        {             return _context.BookTbls.Find(bookId);
        }
        public void InsertBook(BookTbl book)
        {
            _context.BookTbls.Add(book);
        }
        public void DeleteBook(int bookId)
        {
            BookTbl book = _context.BookTbls.Find(bookId);
            _context.BookTbls.Remove(book);
        }
        public void UpdateBook(BookTbl book)
        {
            _context.Entry(book).State = EntityState.Modified;
        }
        public void Save()
        {
            _context.SaveChanges();
        }
    }
}

3). Add a new class file "Print" in the model folder

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Web;
namespace MvcRepo.Models
{
    public class Print
    {
        public static Dictionary<string, string> Table = new Dictionary<string, string>();
        public static DataTable ToDataTable<T>(List<T> items, Dictionary<string, string> CustomeColumn)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            ////Get all the properties
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                foreach (KeyValuePair<string, string> cust in CustomeColumn)
                {
                    if (cust.Key == prop.Name)
                        dataTable.Columns.Add(cust.Value);
                }
            }
            foreach (T item in items)
            {
                var values = new object[dataTable.Columns.Count];
                for (int i = 0; i < Props.Length; i++)
                {
                    foreach (KeyValuePair<string, string> cust in CustomeColumn)
                  {
                      if (cust.Key == Props[i].Name)
                      {
                          int index = dataTable.Columns[cust.Value].Ordinal;
                          values[index] = Props[i].GetValue(item, null);
                      }
                   }
                }
                dataTable.Rows.Add(values);
            }
           //put a breakpoint here and check datatable
           return dataTable;
        }
    }
}

4). Add a cs file HomeController.cs in Controller folder

using MvcRepo.Models;

using MvcRepo.Models.DAL;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Helpers;
using System.Web.Mvc;

namespace MvcRepo.Controllers
{
    public class HomeController : Controller
    {
        private IBookRepository interfaceobj;
        public HomeController()
        {
            interfaceobj = new BookRepository(new DataContext());
        }
        public ActionResult Index()
        {
            var data = from m in interfaceobj.GetBooks() select m;
            return View(data);
        }
        public ActionResult PrintReport()
        {   Export();
            return RedirectToAction(string.Format("/Images/Temp/{0}.xls", "demo"));
        }
        public void Export()
        {
            DataContext db = new DataContext();
            var data = db.BookTbls.ToList();
            //Print.Table);
            WebGrid grid = new WebGrid(source: data, canPage: false, canSort: false);
            List<WebGridColumn> columns = new List<WebGridColumn>();
            foreach (KeyValuePair<string, string> item in Print.Table)
            {
                columns.Add(new WebGridColumn() { ColumnName = item.Key, Header = item.Value });
            }             string gridData = grid.GetHtml(
            columns: columns).ToString();
            Response.ClearContent();
            //give name to excel sheet.
            Response.AddHeader("content-disposition", "attachment; filename=UserData.xls");
            //specify content type
            Response.ContentType = "application/excel";

            //write excel data using this method
            Response.Write(gridData);
            if (!Directory.Exists(Server.MapPath("~/Images/Temp")))
                Directory.CreateDirectory(Server.MapPath("~/Images/Temp"));
            string pdfPath = Server.MapPath(string.Format("~/Images/Temp/{0}.xls", "demo"));
            StreamWriter wr = new StreamWriter(pdfPath);
            wr.Write(Response.OutputStream);
            wr.Close();
            Response.End();
        }
    }
}

5). Add an "Index" file in the View folder

@model IEnumerable<MvcRepo.Models.BookTbl>
@{  Layout = null; }
<script src="~/Scripts/jquery-1.8.2.min.js"></script>
@using MvcRepo.Models;
@using MvcRepo.Models;
@{
    WebGrid grid = new WebGrid(Model, canPage: true, rowsPerPage: 5, selectionFieldName: "selectedRow", ajaxUpdateContainerId: "gridContent");
    grid.Pager(WebGridPagerModes.All);
    List<WebGridColumn> listColumn = new[]{
        grid.Column("ID", "ID"),
        grid.Column("BookName", "BookName"),
        grid.Column("BookAuthor", "BookAuthor") ,
        grid.Column(format: (item) => @Html.ActionLink("Details","home", new {@id = @item.ID}))}.ToList();
}
@grid.GetHtml(tableStyle: "webGrid", headerStyle: "header", alternatingRowStyle: "alt", selectedRowStyle: "select", columns: listColumn)
@Html.ActionLink("Print", "PrintReport", "Home", new { @class = "ExportToExcel" })
@{
    Print.Table.Clear();
    foreach (var item in listColumn)
    {
        if (!string.IsNullOrEmpty(item.ColumnName) && !string.IsNullOrEmpty(item.Header))
        {
            Print.Table.Add(item.ColumnName, item.Header);
        }
    }

How Can Export Data from WebGrid to Excel in MVC

Thank You...


Updated 19-Apr-2020
I am a content writter !

Leave Comment

Comments

Liked By